package br.com.cfccultura.admin.model.dao.mysql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.commons.fileupload.FileItem;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import br.com.cfccultura.admin.model.dao.IAlunoDAO;
import br.com.cfccultura.admin.model.dao.ICursoDAO;
import br.com.cfccultura.admin.model.dao.ITurmaDAO;
import br.com.cfccultura.admin.model.enums.CategoriaCnh;
import br.com.cfccultura.admin.model.enums.Sexo;
import br.com.cfccultura.admin.model.enums.SimNao;
import br.com.cfccultura.admin.model.enums.UF;
import br.com.cfccultura.admin.model.vo.Aluno;
import br.com.cfccultura.admin.model.vo.Anexo;
import br.com.cfccultura.admin.model.vo.Funcionario;
import br.com.cfccultura.admin.model.vo.Matricula;

@Repository
public class AlunoDAO extends DAO implements IAlunoDAO {

	@Autowired
	private ICursoDAO cursoDAO;
	
	@Autowired
	private ITurmaDAO turmaDAO;
	
	@Override
	public List<Aluno> consultar(Aluno vo) {

		String sql = "SELECT aluno.cd_aluno, aluno.dc_nome, aluno.dc_cpf, aluno.dc_rg, aluno.dc_orgao_expeditor, aluno.dc_endereco, aluno.dc_numero, aluno.dc_complemento, aluno.dc_bairro, aluno.dc_cidade, aluno.sg_uf, aluno.dc_cep, " +
				"aluno.dc_telefone_residencia, aluno.dc_telefone_celular, aluno.dc_telefone_comercial, aluno.dc_email, aluno.id_sexo, aluno.id_deficiente_fisico, aluno.dt_nascimento, aluno.dc_categoria_cnh, aluno.dc_pgu, aluno.dt_primeira_habilitacao, " +
				"aluno.dt_ultima_visita, aluno.dt_cadastro, aluno.cd_funcionario_cadastro, funcionario.nm_funcionario FROM tb_aluno aluno, tb_funcionario funcionario where ";
		List<Object> params = new ArrayList<Object>();
		if(vo.getCodigo() != null) {
			sql += "aluno.cd_aluno = ? and ";
			params.add(vo.getCodigo());
		} else if(vo.getCpf() != null) {
			sql += "aluno.dc_cpf = ? and ";
			params.add(vo.getCpf());
		}
		
		sql += "aluno.cd_funcionario_cadastro = funcionario.cd_funcionario order by aluno.dc_nome";

		List<Aluno> result = query(sql, params.toArray(), new RowMapper<Aluno>() {
			@Override
			public Aluno mapRow(ResultSet rs, int arg1) throws SQLException {
				Aluno vo = new Aluno();
				vo.setCodigo(rs.getInt("aluno.cd_aluno"));
				vo.setNome(rs.getString("aluno.dc_nome"));
				vo.setCpf(rs.getString("aluno.dc_cpf"));
				vo.setRg(rs.getString("aluno.dc_rg"));
				vo.setOrgaoExpeditor(rs.getString("aluno.dc_orgao_expeditor"));
				vo.setEndereco(rs.getString("aluno.dc_endereco"));
				vo.setNumero(rs.getString("aluno.dc_numero"));
				vo.setComplemento(rs.getString("aluno.dc_complemento"));
				vo.setBairro(rs.getString("aluno.dc_bairro"));
				vo.setCidade(rs.getString("aluno.dc_cidade"));
				vo.setUf(UF.valueOf(rs.getString("aluno.sg_uf")));
				vo.setCep(rs.getString("aluno.dc_cep"));
				vo.setTelefoneResidencia(rs.getString("aluno.dc_telefone_residencia"));
				vo.setTelefoneCelular(rs.getString("aluno.dc_telefone_celular"));
				vo.setTelefoneComercial(rs.getString("aluno.dc_telefone_comercial"));
				vo.setEmail(rs.getString("aluno.dc_email"));
				vo.setSexo(Sexo.setValue(rs.getInt("aluno.id_sexo")));
				vo.setDeficienteFisico(SimNao.setValue(rs.getInt("aluno.id_deficiente_fisico")));
				vo.setDataNascimento(rs.getDate("aluno.dt_nascimento"));
				vo.setPgu(rs.getString("aluno.dc_pgu"));
				if(rs.getString("aluno.dc_categoria_cnh") != null) {
					vo.setCategoriaCnh(CategoriaCnh.valueOf(rs.getString("aluno.dc_categoria_cnh")));
				}
				if(rs.getDate("aluno.dt_primeira_habilitacao") != null) {
					vo.setDataPrimeiraHabilitacao(rs.getDate("aluno.dt_primeira_habilitacao"));
				}
				vo.setDataUltimaVisita(rs.getDate("aluno.dt_ultima_visita"));
				vo.setDataCadastro(rs.getDate("aluno.dt_cadastro"));
				vo.setFuncionarioCadastro(new Funcionario(rs.getInt("aluno.cd_funcionario_cadastro")));
				vo.getFuncionarioCadastro().setNome(rs.getString("funcionario.nm_funcionario"));
				
				return vo;
			}
			
		});		
		
		return result;
	}
	
	@Override
	public Aluno consultar(Integer codigo) {
		List<Aluno> list = consultar(new Aluno(codigo));
		if(list.size() > 0)
			return list.get(0);
		return null;
	}
	
	@Override
	public Aluno consultar(String cpf) {
		List<Aluno> list = consultar(new Aluno(cpf));
		if(list.size() > 0)
			return list.get(0);
		return null;
	}
	
	@Override
	public void incluir(final Aluno vo) {
		
		KeyHolder keyHolder = new GeneratedKeyHolder();
		update(new PreparedStatementCreator() {			
			@Override
			public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
				String sql = "INSERT INTO tb_aluno(dc_nome, dc_cpf, dc_rg, dc_orgao_expeditor, dc_endereco, dc_numero, dc_complemento, dc_bairro, dc_cidade, sg_uf, dc_cep, " +
						"dc_telefone_residencia, dc_telefone_celular, dc_telefone_comercial, dc_email, id_sexo, id_deficiente_fisico, " +
						"dt_nascimento, dc_pgu, dc_categoria_cnh, dt_primeira_habilitacao, dt_ultima_visita, dt_cadastro, cd_funcionario_cadastro) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
				PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
				ps.setString(1, vo.getNome());
				ps.setString(2, vo.getCpf());
				ps.setString(3, vo.getRg());
				ps.setString(4, vo.getOrgaoExpeditor());
				ps.setString(5, vo.getEndereco());
				ps.setString(6, vo.getNumero());
				ps.setString(7, vo.getComplemento());
				ps.setString(8, vo.getBairro());
				ps.setString(9, vo.getCidade());
				ps.setString(10, vo.getUf().name());
				ps.setString(11, vo.getCep());
				ps.setString(12, vo.getTelefoneResidencia());
				ps.setString(13, vo.getTelefoneCelular());
				ps.setString(14, vo.getTelefoneComercial());
				ps.setString(15, vo.getEmail());
				ps.setInt(16, vo.getSexo().getValue());
				ps.setInt(17, vo.getDeficienteFisico().getValue());
				ps.setDate(18, new java.sql.Date(vo.getDataNascimento().getTime()));
				ps.setString(19, vo.getPgu());
				ps.setString(20, vo.getCategoriaCnh() != null ? vo.getCategoriaCnh().name() : null);
				if(vo.getDataPrimeiraHabilitacao() != null) {
					ps.setDate(21, new java.sql.Date(vo.getDataPrimeiraHabilitacao().getTime()));
				} else {
					ps.setDate(21, null);
				}
				ps.setDate(22, new java.sql.Date(System.currentTimeMillis()));
				ps.setDate(23, new java.sql.Date(System.currentTimeMillis()));
				ps.setInt(24, vo.getFuncionarioCadastro().getCodigo());					 
			    return ps;
			}
		}, keyHolder);
		
		vo.setCodigo(keyHolder.getKey().intValue());
	
	}
	
	@Override
	public void alterar(Aluno vo) {
		String sql = "UPDATE tb_aluno set dc_nome = ?, dc_rg = ?, dc_orgao_expeditor = ?, dc_endereco = ?, dc_numero = ?, dc_complemento = ?, dc_bairro = ?, dc_cidade = ?, " +
				"sg_uf = ?, dc_cep = ?, dc_telefone_residencia = ?, dc_telefone_celular = ?, dc_telefone_comercial = ?, " +
				"dc_email = ?, id_sexo = ?, id_deficiente_fisico = ?, dt_nascimento = ?, dc_pgu = ?, dc_categoria_cnh = ?, dt_primeira_habilitacao = ?, dt_ultima_visita  = ?, cd_funcionario_cadastro = ? where cd_aluno = ?";
		update(sql, new Object[] {
			vo.getNome(),
			vo.getRg(),
			vo.getOrgaoExpeditor(),
			vo.getEndereco(),
			vo.getNumero(),
			vo.getComplemento(),
			vo.getBairro(),
			vo.getCidade(),
			vo.getUf().name(),
			vo.getCep(),
			vo.getTelefoneResidencia(),
			vo.getTelefoneCelular(),
			vo.getTelefoneComercial(),
			vo.getEmail(),
			vo.getSexo().getValue(),
			vo.getDeficienteFisico().getValue(),
			vo.getDataNascimento(),
			vo.getPgu(),
			vo.getCategoriaCnh() != null ? vo.getCategoriaCnh().name() :  null,
			vo.getDataPrimeiraHabilitacao(),			
			new Date(),
			vo.getFuncionarioCadastro().getCodigo(),
			vo.getCodigo()
		});
	}
	
	@Override
	public Anexo incluirAnexo(final Aluno vo, final FileItem file) {
		
		KeyHolder keyHolder = new GeneratedKeyHolder();
		update(new PreparedStatementCreator() {
			@Override
			public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
				String sql = "INSERT INTO tb_aluno_anexo(cd_aluno, nm_anexo, bn_anexo, dt_cadastro, cd_funcionario_cadastro) values(?,?,?,?,?)";
				PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
				ps.setInt(1, vo.getCodigo());
				ps.setString(2, file.getName());
				ps.setBytes(3, file.get());
				ps.setDate(4, new java.sql.Date(System.currentTimeMillis()));
				ps.setInt(5, vo.getFuncionarioCadastro().getCodigo());			 
			    return ps;
			}
		}, keyHolder);
		
		Anexo anexo = new Anexo();
		anexo.setCodigo(keyHolder.getKey().intValue());
		anexo.setNome(file.getName());
		
		return anexo;
	}
	
	@Override
	public Anexo getAnexo(final Integer codigo) {
		String sql = "SELECT nm_anexo, bn_anexo, dt_cadastro FROM tb_aluno_anexo WHERE cd_aluno_anexo = ?";
		return queryForObject(sql, new Object[]{codigo}, new RowMapper<Anexo>() {
			@Override
			public Anexo mapRow(ResultSet rs, int arg1) throws SQLException {
				Anexo vo = new Anexo();
				vo.setCodigo(codigo);
				vo.setNome(rs.getString("nm_anexo"));
				vo.setBinario(rs.getBytes("bn_anexo"));
				vo.setCadastro(rs.getDate("dt_cadastro"));
				return vo;
			}
		});
	}
	
	@Override
	public List<Anexo> consultaListaAnexos(Integer codigo) {
		String sql = "SELECT cd_aluno_anexo, nm_anexo, dt_cadastro FROM tb_aluno_anexo WHERE cd_aluno = ?";
		return query(sql, new Object[]{codigo}, new RowMapper<Anexo>() {
			@Override
			public Anexo mapRow(ResultSet rs, int arg1) throws SQLException {
				Anexo vo = new Anexo();
				vo.setCodigo(rs.getInt("cd_aluno_anexo"));
				vo.setNome(rs.getString("nm_anexo"));
				vo.setCadastro(rs.getDate("dt_cadastro"));
				return vo;
			}
		});
	}
	
	@Override
	public void excluir(Integer codigo) {
		String sql = "DELETE FROM tb_aluno_anexo WHERE cd_aluno_anexo = ?";
		update(sql, new Object[]{codigo});
		
	}
	
	@Override
	public List<Matricula> consultaHistoricoCursoRealizado(Aluno vo) {
		String sql = "SELECT matricula.cd_turma_matricula, turma.cd_turma, turma.cd_curso, turma.dt_inicio, instrutor.nm_funcionario " +
				"FROM tb_turma_matricula matricula, tb_turma turma, tb_funcionario instrutor where ";
		List<Object> params = new ArrayList<Object>();
		if(vo.getCodigo() != null) {
			sql += "matricula.cd_aluno = ? and ";
			params.add(vo.getCodigo());
		}
		
		sql += "matricula.cd_turma = turma.cd_turma and turma.cd_funcionario_instrutor = instrutor.cd_funcionario " +
				"order by matricula.dt_cadastro";

		List<Matricula> result = query(sql, params.toArray(), new RowMapper<Matricula>() {
			@Override
			public Matricula mapRow(ResultSet rs, int arg1) throws SQLException {
				Matricula vo = new Matricula();
				vo.setCodigo(rs.getInt("matricula.cd_turma_matricula"));
				vo.setTurma(getTurmaDAO().consultar(rs.getInt("turma.cd_turma")));
				vo.getTurma().setCurso(getCursoDAO().consultar(rs.getInt("turma.cd_curso")));
				vo.getTurma().setInicio(rs.getDate("turma.dt_inicio"));
				vo.getTurma().setInstrutor(new Funcionario());
				vo.getTurma().getInstrutor().setNome(rs.getString("instrutor.nm_funcionario"));				
				return vo;
			}			
		});
		
		return result;
	}
	
	public ICursoDAO getCursoDAO() {
		return cursoDAO;
	}
	public void setCursoDAO(ICursoDAO cursoDAO) {
		this.cursoDAO = cursoDAO;
	}
	public ITurmaDAO getTurmaDAO() {
		return turmaDAO;
	}
	public void setTurmaDAO(ITurmaDAO turmaDAO) {
		this.turmaDAO = turmaDAO;
	}

}
